AZUPPER ( Left ( Trim ( Middle (CityStateZip, Position (CityStateZip, "," , 0) + 1, 10)),2))
IF ( NoZip = "No Zip" , "00000" , IF ( Left ( Right (CityStateZip, 5 ) , 1) = "-", Right ( Trim (CityStateZip), 10), Right ( Trim (CityStateZip), 5)))
No Zip
00000&
Serial #B
ial #B
ial #B
NoZipB
AhIF ( Length ( Trim (Middle ( CityStateZip, Position (CityStateZip, ",", 1 ) , 99))) < 7 , "No Zip" , "")
No Zip'
A ErrorTestB
IF ( (Length ( TextToNum (zip)) < 5 AND Left (Zip,1)
"0" ) OR Zip = "00000" OR (Length (Int ( Right (CityStateZip, 10))) > 5 AND Length (Zip) < 10), "Zip Code", IF ( Position (CityStateZip, ",", 0) < 1, "No
J , If (State & null = null OR Length ( Trim (State)) < 2 , "State", ""))))
Serial #
CityStateZip
Clean Out Database
Instructions
Find Problems
Show All
Problems
Problems
CreditB
37830-9999E
6W#P0Hn
PublicB
Helvetica
Geneva
User Dictionary
PBF`: n
Comma
State
Missouri
Nebraska
Tennessee
Unknown
Virginia
tate'
State'
STATEFULLNAME
YOURZIP
No comma&
City&
State'
Main Dictionary
37830
Ridge
DAZZLING
E DATABASES
The Jackson-Crockett Company
FAX: (703) 922-6018
E-mail: Nash Cat@aol.com
FileMaker Pro Specialists
Member, Claris Solutions Alliance
Instructions
InstructionsB
nullB
NameB
CompanyB
AddressB
A CityStateB
YourZipB
Splitter
Credit
Instructions
Survey
SplitterB
To Splitter
Clean Out Database
Import & Lookup
Find Problems
Print Instructions
Print Survey
Find Problems
Find Problems
Print Instructions
Clean Out Database
Print Survey
Find Problems
Helvetica
Geneva
To Splitter
Notes
1. The parsing is based on a series of calculations. The presence of a comma between the city and state in the original data is critical. An absent comma will be flagged as a problem. The calculations wiB
ll tolerate up to eight spaces between the comma and the state name.
2. This Splitter will accommodate five- or nine-digit ZIP Codes. More than five digits without a hyphen will raise a Problem flag.
3. This
InstructionsB
DARING
DASHING
DARING
Virginia
New York, NY 2234B
New YorkC
2234E
Zip Code[
New York
Arlington, V 22219B ArlingtonC
22219E
State[
UNKNOWN
Jackson, TNN 38301B
JacksonC
38301E
5[ Tennessee
Comma
State
Missouri
Nebraska
Tennessee
Unknown
Virginia
6W#P0Hn
PublicB
Helvetica
Geneva
6W#P0Hn
PublicB
Helvetica
Geneva
Nebraska
6W#P0Hn
PublicB
Helvetica
Geneva
Washington
00000
01788
10036
20001
22212
22219
22310-1761
24567
37830
38301
Comma
State
Missouri
Nebraska
Tennessee
Unknown
Virginia
0-999
37111
37830-9999
To Splitter
PublicB
Helvetica
Geneva
Print Instructions
PublicB
Helvetica
Geneva
Import & Lookup
6W#P0Hn
PublicB
Helvetica
Geneva
No Comma&
City&
State'
y" , If (State & null = null OR Length ( Trim (State)) < 2, "State", ""))))
No Comma&
City&
State'
5. The built-in error detection (as reflected in the Problems column) isn
t foolproof. Please edit carefully if absolute accuracy is important.
6. This Splitter will work without the States.FM database, butF
it will not be as effective at spotting faulty state abbreviations and you won
t have the option of using unabbreviated state names.
Splitter won
t handle foreign addresses. Mailing conventions vary too much from country to country. In these cases, you
ll need to split the addresses manually in the original database. Do that after you have fD
inished splitting the domestic addresses here. Otherwise, your labor might be wiped out in the process.
4. If you are familiar with FileMaker Pro, you may modify the layouts and scripts to your heart
s content
to your heart
s content.
Instructions
1. In your original database, create four fields: Serial Number, City, State and Zip. The City, State and Zip fields should be Lookups based on the Serial Number. The Lookup file will be this SpliB
tter.
2. Delete all data currently in this Splitter.
3. Import two fields into this Splitter:
The original field containing the city, state and Zip Code.
The Serial Number.
4. As you import the data, the
Splitter will parse the city, state and Zip. Afterwards, it will automatically look up all of the states in the States.FM database that should accompany this Splitter. This will spot any invalid state names. ID
t also puts the full state name in a field called StateFullName, which gives you the option of using unabbreviated state names in your original database if you choose.
5. Review the parsed data. This Splitter
original database.
will flag many errors in the right-hand column. Edit the original data in the CityStateZip column. If you get it right there, the Splitter will parse it correctly.
6. Return to your original database. Click inF
the Serial Number field and then select Relookup from the Edit menu. FileMaker will then put the parsed data into the new fields of your original database.
se. Find all
Print These Instructions
The Jackson-Crockett Company
FAX: (703) 922-6018
E-mail: Nash Cat@aol.com
FileMaker Pro Specialists
Member, Claris Solutions Alliance
EDIT IN THIS COLUMN
Credit
Import
State Unabbreviated
mber, Claris Solutions Alliance
Clean Out Database
PublicB
Helvetica
Geneva
SurveyB
CityStateZipB
CityB
AATrim ( Left (CityStateZip, Position (CityStateZip, "," , 0) - 1))
eZip, "," , 0) - 1))
FM VersionsB
Macintosh
Windows
Macs In OfficeB
ConsultantB
Instructions
Survey
SplitterB
Washington, DC 20001B
WashingtonC
20001E
, TN 22212C
22212E
City[ Tennessee
Vandalia, MOB
VandaliaC
00000E
No ZipH
Zip Code[
Missouri
New York NY 10036C
10036E
No comma[
Nebraska
Bangor, 01788B
BangorC
01788E
State[
UNKNOWN
New York, NY 200124567B
New YorkC
24567E
Zip Code[
New York
23456[
Nebraska
FM UseB
A A lot
A little bit
Just learning
SourceB
America Online
Claris
Credit
Instructions
Survey
SplitterB
E"How much do you use FileMaker Pro?
EdDo you currently have adequate support (in-house or from a consultant) for your FileMaker databases?
City, State
Address
Your Company
Your Name
Source
Oak Ridge, TN 37830B Oak RidgeC
37830E
1[ Tennessee
Alexandria, VA 22310-1761B
AlexandriaC
22310-1761E
New York, NY 2234B
New YorkC
2234E
Zip Code
Arlington, V 22219B ArlingtonC
22219E
State
Jackson, TNN 38301B
JacksonC
38301E
Survey
ELYour only obligation for using this Splitter is to complete a simple survey.
Copyright 1994, The Jackson-Crockett Co. All Rights Reserved. CityStateZip Splitter may be distributed without charge as long as this credit layout is included.
Print Survey
6W#P0Hn
PublicB
Helvetica
Geneva
E-Which version(s) of FileMaker Pro do you use?
City, State
Address
Company
E Your Name
E0Where did you obtain this CityStateZip Splitter?
EXYour only obligation for using this Splitter is to complete and return the survey below.
Thank you!
Proceed
Proceed
ModemB
A3No modem
1200 baud
2400 baud
9600 baud
14400 baud +
PhoneB
StateFullNameB
5: States.FMbK
UNKNOWN
emailB
missing hyphen'
Pro 2.0F!
Pro 2.0 - 2.1I
Main Dictionary
Washington@
STATEFULLNAME
Print Survey
AoPlease FAX to:
(703) 922-6018
Or mail to:
The Jackson-Crockett Company
P.O. Box 9178
Arlington, VA 22219-1178
Phone
E-mail
Relookup States
6W#P0Hn
PublicB
Helvetica
Geneva
Splitter
Credit
Instructions
Survey
SplitterB
Thank you!
Proceednswer based on the fastest.)
Thank you!
Proceed
EUDo you have a modem? (If you have more than one, please answer based on the fastest.)
comma" , If (City & null = null OR Length (City) < 2 , "City" , If (State & null = null OR Length ( Trim (State)) < 2 OR StateFullName = "Unknown" , "State", ""))))
(.txt)